iT邦幫忙

0

sql語法七(刪除大量資料的語法)

  • 分享至 

  • xImage
  •  

參考James Fu 的技術學習之路網址:
https://dotblogs.com.tw/jamesfu/2015/05/17/sp_largedelete

原始語法如下:

CREATE PROCEDURE [dbo].[sp_LargeDelete] 
	@TableName sysname, 
	@MaxRows int = 100000,
	@Filter nvarchar(512) = ''
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQL nvarchar(512)

	BEGIN TRY
		IF ( @Filter = '' OR @Filter is null )
		BEGIN
			SET @SQL = 'TRUNCATE TABLE '+@TableName ;
			exec sp_executesql @SQL
		END
		
        ELSE
		
        BEGIN
			DECLARE @Count INT = -1
			SET @SQL = 'DELETE TOP ('+CAST(@MaxRows AS varchar) + ') FROM  ' + @TableName + ' WHERE ' + @Filter + ' OPTION ( MAXDOP 1 )' ;
			WHILE @Count <> 0
			BEGIN
				BEGIN TRAN
				exec sp_executesql @SQL
				SET @Count = @@ROWCOUNT
				COMMIT
			END
		END
	END TRY
	BEGIN CATCH
		PRINT ERROR_MESSAGE()
		IF @@TRANCOUNT > 0
		ROLLBACK
	END CATCH
END

圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言